﻿using iTool.Common.Options;
using Orleans;
using Orleans.Concurrency;
using Orleans.Providers;
using Orleans.Runtime;
using Orleans.Storage;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace iTool.ClusterComponent
{
    public interface IZSetStorageService : iToolServiceWithStringKey
    {
        Task<ZSetState> GetStateAsync();
        Task<int> GetVersionAsync();
        Task SetAsync(string bytes, long score);
        Task<SortedList<long, KeyValuePair<int, string>>> GetAsync();
        Task<string> GetByScoreAsync(long score);
        Task<string> GetByIndexAsync(int index);
        Task<ICollection<string>> GetRangeAsync(long startScore,long endScore);
        Task RemoveByIndexAsync(int index);
        Task RemoveByScoreAsync(long score);
        Task RemoveRangeAsync(long startScore, long endScore);
        Task RemoveAsync(string bytes);
        Task RemoveAsync();
    }


    [StorageProvider(ProviderName = "ZSetStorageService")]
    public class ZSetStorageService : Grain<ZSetState>, IZSetStorageService, IGrainStorage
    {
        readonly AdoNetOptions options;
        string connection;
        public ZSetStorageService(AdoNetOptions options)
        {
            this.options = options;
            this.connection = this.options.GetConnection();
        }
        public async Task ClearStateAsync(string grainType, GrainReference grainReference, IGrainState grainState)
        {
            await using (SqlConnection conn = new SqlConnection(this.connection))
            {
                string read = "SELECT top 1 id from ClusterCacheZSetTable where [key] = @key";
                string deleteValue = "DELETE ClusterCacheZSetValueTable where listID = @id";
                string delete = "DELETE ClusterCacheZSetTable where [key] = @key";

                await conn.OpenAsync();
                int id = 0;
                await using (SqlCommand command = new SqlCommand(read, conn))
                {
                    command.Parameters.AddRange(new SqlParameter[]
                    {
                            new SqlParameter("key",grainReference.GetPrimaryKeyString())
                    });
                    int.TryParse((await command.ExecuteScalarAsync()).ToString(), out id);
                }

                if (id > 0)
                {
                    try
                    {
                        await using (SqlCommand command = new SqlCommand(deleteValue, conn))
                        {
                            command.Parameters.AddRange(new SqlParameter[]
                            {
                                new SqlParameter("id",id)
                            });
                            await command.ExecuteNonQueryAsync();
                        }
                        await using (SqlCommand command = new SqlCommand(delete, conn))
                        {
                            command.Parameters.AddRange(new SqlParameter[]
                            {
                                new SqlParameter("key",grainReference.GetPrimaryKeyString())
                            });
                            await command.ExecuteNonQueryAsync();
                        }
                    }
                    catch (System.Exception)
                    {
                        throw;
                    }
                }
            }
        }
        public async Task ReadStateAsync(string grainType, GrainReference grainReference, IGrainState grainState)
        {

            string key = grainReference.GetPrimaryKeyString();

            await using (SqlConnection conn = new SqlConnection(this.connection))
            {
                string read = "SELECT top 1 id from ClusterCacheZSetTable where [key] = @key";
                string readValue = "SELECT value,sortIndex,id FROM ClusterCacheZSetValueTable where listID = @id";

                await conn.OpenAsync();
                int id = 0;
                await using (SqlCommand command = new SqlCommand(read, conn))
                {
                    command.Parameters.AddRange(new SqlParameter[]
                    {
                        new SqlParameter("key",key)
                    });
                    int.TryParse(((await command.ExecuteScalarAsync()) ?? "").ToString(), out id);
                }

                if (id > 0)
                {
                    await using (SqlCommand command = new SqlCommand(readValue, conn))
                    {
                         var sortedList = new SortedList<long, KeyValuePair<int, string>>();

                        command.Parameters.AddRange(new SqlParameter[]
                        {
                            new SqlParameter("id",id)
                        });

                        var reader = await command.ExecuteReaderAsync();
                        while (await reader.ReadAsync())
                        {
                            sortedList.TryAdd((int)reader["sortIndex"], new KeyValuePair<int, string>((int)reader["id"], reader["value"] as string));
                        }

                        grainState.State = new ZSetState
                        {
                            Id = id,
                            keyValuePairs = sortedList
                        }; 
                    }
                }
                else
                {
                    await using (SqlCommand command = new SqlCommand("insert into ClusterCacheZSetTable([key]) values(@key); select @identityV=@@IDENTITY", conn))
                    {
                        var output = new SqlParameter("@identityV", System.Data.SqlDbType.Int);
                        output.Direction = System.Data.ParameterDirection.Output;
                        command.Parameters.AddRange(new SqlParameter[]
                        {
                            new SqlParameter("key",key),
                            output
                        });
                        await command.ExecuteNonQueryAsync();
                        grainState.State = new ZSetState
                        {
                            Id = (int)output.Value,
                            keyValuePairs = new SortedList<long, KeyValuePair<int, string>>()
                        };
                    }
                }
            }

        }
        public async Task WriteStateAsync(string grainType, GrainReference grainReference, IGrainState grainState)
        {
            await Task.CompletedTask;
        }

        public async Task ClearStateAsync(int id)
        {
            await using (SqlConnection conn = new SqlConnection(this.connection))
            {
                await using (SqlCommand command = new SqlCommand($"delete ClusterCacheZSetValueTable where id = @id", conn))
                {
                    command.Parameters.AddRange(new SqlParameter[]
                    {
                            new SqlParameter("id",id)
                    });
                    await conn.OpenAsync();
                    await command.ExecuteNonQueryAsync();
                }
            }
        }
        public async Task ClearStateAsync(ICollection<int> ids)
        {
            await using (SqlConnection conn = new SqlConnection(this.connection))
            {
                await using (SqlCommand command = new SqlCommand($"delete ClusterCacheZSetValueTable where id in ({string.Join(',', ids)})", conn))
                {
                    await conn.OpenAsync();
                    await command.ExecuteNonQueryAsync();
                }
            }
        }
        public async Task ClearStateAsync(string paload)
        {
            await using (SqlConnection conn = new SqlConnection(this.connection))
            {
                await using (SqlCommand command = new SqlCommand($"delete ClusterCacheZSetValueTable where listID=@id and  value = @paload", conn))
                {
                    command.Parameters.AddRange(new SqlParameter[]
                    {
                            new SqlParameter("id",this.State.Id),
                            new SqlParameter("paload",paload)
                    });
                    await conn.OpenAsync();
                    await command.ExecuteNonQueryAsync();
                }
            }
        }
        
        public async Task<int> WriteStateAsync(string paload,long sortIndex) 
        {
            await using (SqlConnection conn = new SqlConnection(this.connection))
            {
                await using (SqlCommand command = new SqlCommand($"insert into ClusterCacheZSetValueTable(listID,value,sortIndex) values(@listID,@value,@sortIndex);select @identityV=@@IDENTITY", conn))
                {
                    var output = new SqlParameter("@identityV", System.Data.SqlDbType.Int);
                    output.Direction = System.Data.ParameterDirection.Output;

                    command.Parameters.AddRange(new SqlParameter[]
                    {
                            new SqlParameter("value",paload),
                            new SqlParameter("listID",this.State.Id),
                            new SqlParameter("sortIndex",sortIndex),
                            output
                    });
                    await conn.OpenAsync();
                    await command.ExecuteNonQueryAsync();
                    return (int)output.Value;
                }
            }
        }

        public async Task WriteStateAsync(int id,string paload)
        {
            await using (SqlConnection conn = new SqlConnection(this.connection))
            {
                await using (SqlCommand command = new SqlCommand($"update ClusterCacheZSetValueTable set value = @value where id=@id", conn))
                {
                    command.Parameters.AddRange(new SqlParameter[]
                    {
                            new SqlParameter("value",paload),
                            new SqlParameter("id",id)
                    });
                    await conn.OpenAsync();
                    await command.ExecuteNonQueryAsync();
                }
            }
        }


        public async Task SetAsync(string bytes, long score)
        {
            this.State.Version++;
            this.State.keyValuePairs = this.State.keyValuePairs ?? new SortedList<long, KeyValuePair<int, string>>();
            if (this.State.keyValuePairs.ContainsKey(score))
            {
                var sorte = this.State.keyValuePairs[score];
                this.State.keyValuePairs[score] = new KeyValuePair<int, string>(sorte.Key, bytes);
                await this.WriteStateAsync(sorte.Key, bytes);
            }
            else
            {
                var id = await this.WriteStateAsync(bytes, score);
                this.State.keyValuePairs.Add(score, new KeyValuePair<int, string>(id, bytes));
            }
        }

        public Task<SortedList<long, KeyValuePair<int, string>>> GetAsync()
        {
            return Task.FromResult(this.State.keyValuePairs);
        }

        public Task<string> GetByScoreAsync(long score)
        {
            if (this.State.keyValuePairs?.ContainsKey(score) == true)
            {
                return Task.FromResult(this.State.keyValuePairs[score].Value);
            }
            return Task.FromResult(string.Empty);
        }

        public Task<string> GetByIndexAsync(int index)
        {
            return Task.FromResult(this.State.keyValuePairs?.ElementAtOrDefault(index).Value.Value);
        }

        public Task<ICollection<string>> GetRangeAsync(long startScore, long endScore)
        {
            bool isStartScore = startScore > 0;
            bool isEndScore = endScore > 0;

            ICollection<string> result = new List<string>();

            if (startScore < 0 & endScore < 0)
            {
                return Task.FromResult(result);
            }

            foreach (var item in this.State.keyValuePairs)
            {
                if (isEndScore && isStartScore)
                {
                    if (item.Value.Key <= endScore && item.Value.Key >= startScore)
                    {
                        result.Add(item.Value.Value);
                    }
                }
                else if (isStartScore)
                {
                    if (item.Value.Key >= startScore)
                    {
                        result.Add(item.Value.Value);
                    }
                }
                else if (isEndScore)
                {
                    result.Add(item.Value.Value);
                }
            }
            return Task.FromResult(result);
        }

        public async Task RemoveByIndexAsync(int index)
        {
            if (this.State.keyValuePairs != null)
            {
                var element = this.State.keyValuePairs.ElementAt(index);
                if (element.Value.Key > 0)
                {
                    this.State.Version++;
                    this.State.keyValuePairs.RemoveAt(index);
                    await this.ClearStateAsync(element.Value.Key);
                }
            }
            
        }

        public async Task RemoveByScoreAsync(long score)
        {
            if (this.State.keyValuePairs?.ContainsKey(score) == true)
            {
                this.State.Version++;
                var element = this.State.keyValuePairs[score];
                this.State.keyValuePairs.Remove(score);
                await this.ClearStateAsync(element.Key);
            }
        }

        public async Task RemoveRangeAsync(long startScore, long endScore)
        {
            bool isStartScore = startScore > 0;
            bool isEndScore = endScore > 0;
            ICollection<int> result = new List<int>();
            foreach (var item in this.State?.keyValuePairs)
            {
                if (isEndScore)
                {
                    if (item.Value.Key <= endScore)
                    {
                        this.State.keyValuePairs.Remove(item.Key);
                        result.Add(item.Value.Key);
                    }
                    else
                    {
                        continue;
                    }
                }
                if (isStartScore)
                {
                    if (item.Value.Key >= startScore)
                    {
                        this.State.keyValuePairs.Remove(item.Key);
                        result.Add(item.Value.Key);
                    }
                    else
                    {
                        continue;
                    }
                }
                else
                {
                    result.Add(item.Value.Key);
                }
            }

            if (result.Count > 0)
            {
                this.State.Version++;
                await this.ClearStateAsync(result);
            }
        }

        public async Task RemoveAsync(string bytes)
        {
            this.State.Version++;
            var item = this.State.keyValuePairs.Where(item => item.Value.Value == bytes).FirstOrDefault();
            if (item.Key > 0)
            {
                this.State.keyValuePairs.Remove(item.Key);
            }
            await this.ClearStateAsync(bytes);
        }

        public async Task RemoveAsync()
        {
            await this.ClearStateAsync();
        }
        public Task<ZSetState> GetStateAsync()
        {
            return Task.FromResult(this.State);
        }

        public Task<int> GetVersionAsync()
        {
            return Task.FromResult(this.State.Version);
        }
    }

    public class ZSetState
    {
        public int Id { get; set; }
        public int Version { get; set; }
        public SortedList<long, KeyValuePair<int, string>> keyValuePairs { get; set; }
    }
}
